const sqlite3 = require('sqlite3').verbose();
const path = require('path');

// 数据库文件路径
const dbPath = path.resolve(__dirname, 'schedule.db');

// 创建数据库连接
const db = new sqlite3.Database(dbPath, (err) => {
  if (err) {
    console.error('数据库连接错误:', err.message);
  } else {
    console.log('SQLite数据库连接成功');
    // 初始化数据库表
    initTables();
  }
});

// 初始化数据库表结构
function initTables() {
  // 排班类型表
  db.run(`CREATE TABLE IF NOT EXISTS shift_types (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    cycle INTEGER NOT NULL DEFAULT 0,
    holiday_work_schedule BOOLEAN DEFAULT 0,
    adjust_work_day_schedule BOOLEAN DEFAULT 0,
    applicable_type INTEGER NOT NULL,
    is_enabled BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  )`);

  // 人员表
  db.run(`CREATE TABLE IF NOT EXISTS employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    type INTEGER NOT NULL,
    serial_number INTEGER NOT NULL,
    feishu_user_id TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  )`);

  // 排班结果表
  db.run(`CREATE TABLE IF NOT EXISTS schedules (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    shift_type_id INTEGER,
    employee_id INTEGER,
    schedule_date DATE NOT NULL,
    FOREIGN KEY (shift_type_id) REFERENCES shift_types(id),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    UNIQUE(employee_id, schedule_date)
  )`);

  // 节假日表
  db.run(`CREATE TABLE IF NOT EXISTS holidays (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date DATE NOT NULL UNIQUE,
    name TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  )`);

  console.log('数据库表结构初始化完成');
}

module.exports = db;